{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "39d24a6a",
   "metadata": {},
   "source": [
    "# Deploy a BigQuery ML user churn propensity model to Vertex AI for online predictions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c97e1100",
   "metadata": {},
   "source": [
    "## Learning objectives"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a426ddad",
   "metadata": {},
   "source": [
    "* Explore and preprocess a [Google Analytics 4](https://support.google.com/analytics/answer/7029846) data sample in [BigQuery](https://cloud.google.com/bigquery) for machine learning.  \n",
    "* Train a [BigQuery ML (BQML)](https://cloud.google.com/bigquery-ml) [XGBoost](https://xgboost.readthedocs.io/en/latest/) classifier to predict user churn on a mobile gaming application.\n",
    "* Tune a BQML XGBoost classifier using [BQML hyperparameter tuning features](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-create-boosted-tree).\n",
    "* Evaluate the performance of a BQML XGBoost classifier.\n",
    "* Explain your XGBoost model with [BQML Explainable AI](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-xai-overview) global feature attributions.\n",
    "* Generate batch predictions with your BQML XGBoost model.\n",
    "* Export a BQML XGBoost model to a [Google Cloud Storage](https://cloud.google.com/storage).\n",
    "* Upload and deploy a BQML XGBoost model to a [Vertex AI Prediction](https://cloud.google.com/vertex-ai/docs/predictions/getting-predictions) Endpoint for online predictions."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32a258db",
   "metadata": {},
   "source": [
    "## Introduction"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5d8d5372",
   "metadata": {},
   "source": [
    "In this lab, you will train, evaluate, explain, and generate batch and online predictions with a BigQuery ML (BQML) XGBoost model. You will use a Google Analytics 4 dataset from a real mobile application, Flood it! ([Android app](https://play.google.com/store/apps/details?id=com.labpixies.flood), [iOS app](https://itunes.apple.com/us/app/flood-it!/id476943146?mt=8)), to determine the likelihood of users returning to the application. You will generate batch predictions with your BigQuery ML model as well as export and deploy it to **Vertex AI** for online predictions.\n",
    "\n",
    "[BigQuery ML](https://cloud.google.com/bigquery-ml/docs/introduction) lets you train and do batch inference with machine learning models in BigQuery using standard SQL queries faster by eliminating the need to move data with fewer lines of code. [Vertex AI](https://cloud.google.com/vertex-ai) is Google Cloud's complimentary next generation, unified platform for machine learning development. By developing and deploying BQML machine learning solutions on Vertex AI, you can leverage a scalable online prediction service and MLOps tools for model retraining and monitoring to significantly enhance your development productivity, the ability to scale your workflow and decision making with your data, and accelerate time to value.\n",
    "\n",
    "![BQML Vertex AI](./images/vertex-bqml-lab-architecture-diagram.png \"Vertex BQML Lab Architecture Diagram\")\n",
    "\n",
    "Note: this lab is inspired by and extends [Churn prediction for game developers using Google Analytics 4 (GA4) and BigQuery ML](https://cloud.google.com/blog/topics/developers-practitioners/churn-prediction-game-developers-using-google-analytics-4-ga4-and-bigquery-ml). See that blog post and accompanying tutorial for additional depth on this use case and BigQuery ML. In this lab, you will go one step further and focus on how Vertex AI extends BQML's capabilities through online prediction so you can incorporate both customer churn predictions into decision making UIs such as [Looker dashboards](https://looker.com/google-cloud) but also online predictions directly into customer applications to power targeted interventions such as targeted incentives."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bce45947",
   "metadata": {},
   "source": [
    "### Use case: user churn propensity modeling in the mobile gaming industry"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14102f4b",
   "metadata": {},
   "source": [
    "According to a [2019 study](https://gameanalytics.com/reports/mobile-gaming-industry-analysis-h1-2019) on 100K mobile games by the Mobile Gaming Industry Analysis, most mobile games only see a 25% retention rate for users after the first 24 hours, known and any game \"below 30% retention generally needs improvement\". For mobile game developers, improving user retention is critical to revenue stability and increasing profitability. In fact, [Bain & Company research](https://hbr.org/2014/10/the-value-of-keeping-the-right-customers) found that 5% growth in retention rate can result in a 25-95% increase in profits. With lower costs to retain existing customers, the business objective for game developers is clear: reduce churn and improve customer loyalty to drive long-term profitability.\n",
    "\n",
    "Your task in this lab: use machine learning to predict user churn propensity after day 1, a crucial user onboarding window, and serve these online predictions to inform interventions such as targeted in-game rewards and notifications."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4963f56a",
   "metadata": {},
   "source": [
    "## Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "35ce5269-6546-4e54-809b-8fa6e29471dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip3 install google-cloud-aiplatform --user\n",
    "!pip3 install pyarrow==11.0.0 --user\n",
    "!pip3 install --upgrade google-cloud-bigquery --user\n",
    "!pip3 install --upgrade google-cloud-bigquery-storage --user\n",
    "!pip3 install --upgrade google-cloud-storage --user\n",
    "!pip install db-dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32ae1c8a",
   "metadata": {},
   "source": [
    "**Restart the kernel and ignore the compatibility errors.**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0cbbab34",
   "metadata": {},
   "source": [
    "### Define constants"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "edbc96b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Retrieve and set PROJECT_ID and REGION environment variables.\n",
    "PROJECT_ID = !(gcloud config get-value core/project)\n",
    "PROJECT_ID = PROJECT_ID[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1b1bea8f",
   "metadata": {},
   "source": [
    "**Note:** Replace the <code>REGION</code> with the associated region mentioned in the qwiklabs resource panel."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "935a4807",
   "metadata": {},
   "outputs": [],
   "source": [
    "BQ_LOCATION = 'US'\n",
    "REGION = 'us-central1'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1b1bea9f",
   "metadata": {},
   "source": [
    "### Import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4afdade5",
   "metadata": {},
   "outputs": [],
   "source": [
    "from google.cloud import bigquery\n",
    "from google.cloud import aiplatform as vertexai\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "76acc5de",
   "metadata": {},
   "source": [
    "### Create a GCS bucket for artifact storage"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "abc13d5c",
   "metadata": {},
   "source": [
    "Create a globally unique Google Cloud Storage bucket for artifact storage. You will use this bucket to export your BQML model later in the lab and upload it to Vertex AI."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d7682097",
   "metadata": {},
   "outputs": [],
   "source": [
    "GCS_BUCKET = f\"{PROJECT_ID}-bqmlga4\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c003d940",
   "metadata": {},
   "outputs": [],
   "source": [
    "!gcloud storage buckets create --location=$REGION gs://$GCS_BUCKET"   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd34ed14",
   "metadata": {},
   "source": [
    "### Create a BigQuery dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a93d1373",
   "metadata": {},
   "source": [
    "Next, create a BigQuery dataset from this notebook using the Python-based [`bq` command line utility](https://cloud.google.com/bigquery/docs/bq-command-line-tool). \n",
    "\n",
    "This dataset will group your feature views, model, and predictions table together. You can view it in the [BigQuery](https://pantheon.corp.google.com/bigquery) console."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd775fe7",
   "metadata": {},
   "outputs": [],
   "source": [
    "BQ_DATASET = f\"{PROJECT_ID}:bqmlga4\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "53014527",
   "metadata": {},
   "outputs": [],
   "source": [
    "!bq mk --location={BQ_LOCATION} --dataset {BQ_DATASET}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86c8624c",
   "metadata": {},
   "source": [
    "### Initialize the Vertex Python SDK client"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "af877157",
   "metadata": {},
   "source": [
    "Import the Vertex SDK for Python into your Python environment and initialize it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d992f3c",
   "metadata": {},
   "outputs": [],
   "source": [
    "vertexai.init(project=PROJECT_ID, location=REGION, staging_bucket=f\"gs://{GCS_BUCKET}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "45dc2862",
   "metadata": {},
   "source": [
    "## Exploratory Data Analysis (EDA) in BigQuery"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0cacbd08",
   "metadata": {},
   "source": [
    "This lab uses a [public BigQuery dataset]() that contains raw event data from a real mobile gaming app called **Flood it!** ([Android app](https://play.google.com/store/apps/details?id=com.labpixies.flood), [iOS app](https://itunes.apple.com/us/app/flood-it!/id476943146?mt=8)).\n",
    "\n",
    "The data schema originates from Google Analytics for Firebase but is the same schema as [Google Analytics 4](https://support.google.com/analytics/answer/9358801).\n",
    "\n",
    "Take a look at a sample of the raw event dataset using the query below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "049d2d38",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT_ID\n",
    "\n",
    "SELECT \n",
    "    *\n",
    "FROM\n",
    "  `firebase-public-project.analytics_153293282.events_*`\n",
    "    \n",
    "TABLESAMPLE SYSTEM (1 PERCENT)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32ae1c8a",
   "metadata": {},
   "source": [
    "Note: in the cell above, Jupyterlab runs cells starting with `%%bigquery` as SQL queries. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "301298d2",
   "metadata": {},
   "source": [
    "Google Analytics 4 uses an event based measurement model and each row in this dataset is an event. View the [complete schema](https://support.google.com/analytics/answer/7029846) and details about each column. As you can see above, certain columns are nested records and contain detailed information such as:\n",
    "\n",
    "* app_info\n",
    "* device\n",
    "* ecommerce\n",
    "* event_params\n",
    "* geo\n",
    "* traffic_source\n",
    "* user_properties\n",
    "* items*\n",
    "* web_info*"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47bbd90b",
   "metadata": {},
   "source": [
    "This dataset contains 5.7M events from 15K+ users."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "80c0833a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT_ID\n",
    "\n",
    "SELECT \n",
    "    COUNT(DISTINCT user_pseudo_id) as count_distinct_users,\n",
    "    COUNT(event_timestamp) as count_events\n",
    "FROM\n",
    "  `firebase-public-project.analytics_153293282.events_*`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "764fbeaa",
   "metadata": {},
   "source": [
    "## Dataset preparation in BigQuery"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "751359e5",
   "metadata": {},
   "source": [
    "Now that you have a better sense for the dataset you will be working with, you will walk through transforming raw event data into a dataset suitable for machine learning using SQL commands in BigQuery. Specifically, you will:\n",
    "\n",
    "* Aggregate events so that each row represents a separate unique user ID.\n",
    "* Define the **user churn label** feature to train your model to prediction (e.g. 1 = churned, 0 = returned).\n",
    "* Create **user demographic** features.\n",
    "* Create **user behavioral** features from aggregated application events."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0880f4fa",
   "metadata": {},
   "source": [
    "### Defining churn for each user"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e71540a9",
   "metadata": {},
   "source": [
    "There are many ways to define user churn, but for the purposes of this lab, you will predict 1-day churn as users who do not come back and use the app again after 24 hr of the user's first engagement. This is meant to capture churn after a user's \"first impression\" of the application or onboarding experience.\n",
    "\n",
    "In other words, after 24 hr of a user's first engagement with the app:\n",
    "\n",
    "* if the user shows no event data thereafter, the user is considered **churned**.\n",
    "* if the user does have at least one event datapoint thereafter, then the user is considered **returned**.\n",
    "\n",
    "You may also want to remove users who were unlikely to have ever returned anyway after spending just a few minutes with the app, which is sometimes referred to as \"bouncing\". For example, you will build your model on only on users who spent at least 10 minutes with the app (users who didn't bounce).\n",
    "\n",
    "The query below defines a churned user with the following definition:\n",
    "\n",
    "**Churned = \"any user who spent at least 10 minutes on the app, but after 24 hour from when they first engaged with the app, never used the app again\"**\n",
    "\n",
    "You will use the raw event data, from their first touch (app installation) to their last touch, to identify churned and bounced users in the `user_churn` view query below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ab5c7d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT_ID\n",
    "\n",
    "CREATE OR REPLACE VIEW bqmlga4.user_churn AS (\n",
    "  WITH firstlasttouch AS (\n",
    "    SELECT\n",
    "      user_pseudo_id,\n",
    "      MIN(event_timestamp) AS user_first_engagement,\n",
    "      MAX(event_timestamp) AS user_last_engagement\n",
    "    FROM\n",
    "      `firebase-public-project.analytics_153293282.events_*`\n",
    "    WHERE event_name=\"user_engagement\"\n",
    "    GROUP BY\n",
    "      user_pseudo_id\n",
    "\n",
    "  )\n",
    "  \n",
    "SELECT\n",
    "    user_pseudo_id,\n",
    "    user_first_engagement,\n",
    "    user_last_engagement,\n",
    "    EXTRACT(MONTH from TIMESTAMP_MICROS(user_first_engagement)) as month,\n",
    "    EXTRACT(DAYOFYEAR from TIMESTAMP_MICROS(user_first_engagement)) as julianday,\n",
    "    EXTRACT(DAYOFWEEK from TIMESTAMP_MICROS(user_first_engagement)) as dayofweek,\n",
    "\n",
    "    #add 24 hr to user's first touch\n",
    "    (user_first_engagement + 86400000000) AS ts_24hr_after_first_engagement,\n",
    "    \n",
    "    #churned = 1 if last_touch within 24 hr of app installation, else 0\n",
    "    IF (user_last_engagement < (user_first_engagement + 86400000000),\n",
    "    1,\n",
    "    0 ) AS churned,\n",
    "    \n",
    "    #bounced = 1 if last_touch within 10 min, else 0\n",
    "    IF (user_last_engagement <= (user_first_engagement + 600000000),\n",
    "    1,\n",
    "    0 ) AS bounced,\n",
    "  FROM\n",
    "    firstlasttouch\n",
    "  GROUP BY\n",
    "    user_pseudo_id,\n",
    "    user_first_engagement,\n",
    "    user_last_engagement\n",
    "    );\n",
    "\n",
    "SELECT \n",
    "  * \n",
    "FROM \n",
    "  bqmlga4.user_churn \n",
    "LIMIT 100;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b731ce4c",
   "metadata": {},
   "source": [
    "Review how many of the 15k users bounced and returned below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51cdaa9e",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT_ID\n",
    "\n",
    "SELECT\n",
    "    bounced,\n",
    "    churned, \n",
    "    COUNT(churned) as count_users\n",
    "FROM\n",
    "    bqmlga4.user_churn\n",
    "GROUP BY \n",
    "  bounced,\n",
    "  churned\n",
    "ORDER BY \n",
    "  bounced"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab1fc447",
   "metadata": {},
   "source": [
    "For the training data, you will only end up using data where bounced = 0. Based on the 15k users, you can see that 5,557 ( about 41%) users bounced within the first ten minutes of their first engagement with the app. Of the remaining 8,031 users, 1,883 users ( about 23%) churned after 24 hours which you can validate with the query below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b9ae920e",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT_ID\n",
    "\n",
    "SELECT\n",
    "    COUNTIF(churned=1)/COUNT(churned) as churn_rate\n",
    "FROM\n",
    "    bqmlga4.user_churn\n",
    "WHERE bounced = 0"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aef78f94",
   "metadata": {},
   "source": [
    "### Extract user demographic features"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dfea3b94",
   "metadata": {},
   "source": [
    "There is various user demographic information included in this dataset, including `app_info`, `device`, `ecommerce`, `event_params`, and `geo`. Demographic features can help the model predict whether users on certain devices or countries are more likely to churn.\n",
    "\n",
    "Note that a user's demographics may occasionally change (e.g. moving countries). For simplicity, you will use the demographic information that Google Analytics 4 provides when the user first engaged with the app as indicated by MIN(event_timestamp) in the query below. This enables every unique user to be represented by a single row."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bd36306f",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT_ID\n",
    "\n",
    "CREATE OR REPLACE VIEW bqmlga4.user_demographics AS (\n",
    "\n",
    "  WITH first_values AS (\n",
    "      SELECT\n",
    "          user_pseudo_id,\n",
    "          geo.country as country,\n",
    "          device.operating_system as operating_system,\n",
    "          device.language as language,\n",
    "          ROW_NUMBER() OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp DESC) AS row_num\n",
    "      FROM `firebase-public-project.analytics_153293282.events_*`\n",
    "      WHERE event_name=\"user_engagement\"\n",
    "      )\n",
    "  SELECT * EXCEPT (row_num)\n",
    "  FROM first_values\n",
    "  WHERE row_num = 1\n",
    "  );\n",
    "\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  bqmlga4.user_demographics\n",
    "LIMIT 10"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11abe88b",
   "metadata": {},
   "source": [
    "### Aggregate user behavioral features"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71dfe78b",
   "metadata": {},
   "source": [
    "Behavioral data in the raw event data spans across multiple events -- and thus rows -- per user. The goal of this section is to aggregate and extract behavioral data for each user, resulting in one row of behavioral data per unique user.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b9be082c",
   "metadata": {},
   "source": [
    "As a first step, you can explore all the unique events that exist in this dataset, based on event_name:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "debac29c",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT_ID\n",
    "\n",
    "SELECT\n",
    "  event_name,\n",
    "  COUNT(event_name) as event_count\n",
    "FROM\n",
    "    `firebase-public-project.analytics_153293282.events_*`\n",
    "GROUP BY \n",
    "  event_name\n",
    "ORDER BY\n",
    "   event_count DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6596f7be",
   "metadata": {},
   "source": [
    "For this lab, to predict whether a user will churn or return, you can start by counting the number of times a user engages in the following event types:\n",
    "\n",
    "* user_engagement\n",
    "* level_start_quickplay\n",
    "* level_end_quickplay\n",
    "* level_complete_quickplay\n",
    "* level_reset_quickplay\n",
    "* post_score\n",
    "* spend_virtual_currency\n",
    "* ad_reward\n",
    "* challenge_a_friend\n",
    "* completed_5_levels\n",
    "* use_extra_steps"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60502c0a",
   "metadata": {},
   "source": [
    "In the SQL query below, you will aggregate the behavioral data by calculating the total number of times when each of the above event_names occurred in the data set per user."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b016e18d",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT_ID\n",
    "\n",
    "CREATE OR REPLACE VIEW bqmlga4.user_behavior AS (\n",
    "WITH\n",
    "  events_first24hr AS (\n",
    "    # Select user data only from first 24 hr of using the app.\n",
    "    SELECT\n",
    "      e.*\n",
    "    FROM\n",
    "      `firebase-public-project.analytics_153293282.events_*` e\n",
    "    JOIN\n",
    "      bqmlga4.user_churn c\n",
    "    ON\n",
    "      e.user_pseudo_id = c.user_pseudo_id\n",
    "    WHERE\n",
    "      e.event_timestamp <= c.ts_24hr_after_first_engagement\n",
    "    )\n",
    "SELECT\n",
    "  user_pseudo_id,\n",
    "  SUM(IF(event_name = 'user_engagement', 1, 0)) AS cnt_user_engagement,\n",
    "  SUM(IF(event_name = 'level_start_quickplay', 1, 0)) AS cnt_level_start_quickplay,\n",
    "  SUM(IF(event_name = 'level_end_quickplay', 1, 0)) AS cnt_level_end_quickplay,\n",
    "  SUM(IF(event_name = 'level_complete_quickplay', 1, 0)) AS cnt_level_complete_quickplay,\n",
    "  SUM(IF(event_name = 'level_reset_quickplay', 1, 0)) AS cnt_level_reset_quickplay,\n",
    "  SUM(IF(event_name = 'post_score', 1, 0)) AS cnt_post_score,\n",
    "  SUM(IF(event_name = 'spend_virtual_currency', 1, 0)) AS cnt_spend_virtual_currency,\n",
    "  SUM(IF(event_name = 'ad_reward', 1, 0)) AS cnt_ad_reward,\n",
    "  SUM(IF(event_name = 'challenge_a_friend', 1, 0)) AS cnt_challenge_a_friend,\n",
    "  SUM(IF(event_name = 'completed_5_levels', 1, 0)) AS cnt_completed_5_levels,\n",
    "  SUM(IF(event_name = 'use_extra_steps', 1, 0)) AS cnt_use_extra_steps,\n",
    "FROM\n",
    "  events_first24hr\n",
    "GROUP BY\n",
    "  user_pseudo_id\n",
    "  );\n",
    "\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  bqmlga4.user_behavior\n",
    "LIMIT 10"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77089f30",
   "metadata": {},
   "source": [
    "### Prepare your train/eval/test datasets for machine learning"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a868015",
   "metadata": {},
   "source": [
    "In this section, you can now combine these three intermediary views (`user_churn`, `user_demographics`, and `user_behavior`) into the final training data view called `ml_features`. Here you can also specify bounced = 0, in order to limit the training data only to users who did not \"bounce\" within the first 10 minutes of using the app.\n",
    "\n",
    "Note in the query below that a manual `data_split` column is created in your BigQuery ML table using [BigQuery's hashing functions](https://towardsdatascience.com/ml-design-pattern-5-repeatable-sampling-c0ccb2889f39) for repeatable sampling. It specifies a 80% train | 10% eval | 20% test split to evaluate your model's performance and generalization."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "448138e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT_ID\n",
    "\n",
    "CREATE OR REPLACE VIEW bqmlga4.ml_features AS (\n",
    "    \n",
    "  SELECT\n",
    "    dem.user_pseudo_id,\n",
    "    IFNULL(dem.country, \"Unknown\") AS country,\n",
    "    IFNULL(dem.operating_system, \"Unknown\") AS operating_system,\n",
    "    IFNULL(REPLACE(dem.language, \"-\", \"X\"), \"Unknown\") AS language,\n",
    "    IFNULL(beh.cnt_user_engagement, 0) AS cnt_user_engagement,\n",
    "    IFNULL(beh.cnt_level_start_quickplay, 0) AS cnt_level_start_quickplay,\n",
    "    IFNULL(beh.cnt_level_end_quickplay, 0) AS cnt_level_end_quickplay,\n",
    "    IFNULL(beh.cnt_level_complete_quickplay, 0) AS cnt_level_complete_quickplay,\n",
    "    IFNULL(beh.cnt_level_reset_quickplay, 0) AS cnt_level_reset_quickplay,\n",
    "    IFNULL(beh.cnt_post_score, 0) AS cnt_post_score,\n",
    "    IFNULL(beh.cnt_spend_virtual_currency, 0) AS cnt_spend_virtual_currency,\n",
    "    IFNULL(beh.cnt_ad_reward, 0) AS cnt_ad_reward,\n",
    "    IFNULL(beh.cnt_challenge_a_friend, 0) AS cnt_challenge_a_friend,\n",
    "    IFNULL(beh.cnt_completed_5_levels, 0) AS cnt_completed_5_levels,\n",
    "    IFNULL(beh.cnt_use_extra_steps, 0) AS cnt_use_extra_steps,\n",
    "    chu.user_first_engagement,\n",
    "    chu.month,\n",
    "    chu.julianday,\n",
    "    chu.dayofweek,\n",
    "    chu.churned,\n",
    "    # https://towardsdatascience.com/ml-design-pattern-5-repeatable-sampling-c0ccb2889f39\n",
    "    # BQML Hyperparameter tuning requires STRING 3 partition data_split column.\n",
    "    # 80% 'TRAIN' | 10%'EVAL' | 10% 'TEST'    \n",
    "    CASE\n",
    "      WHEN ABS(MOD(FARM_FINGERPRINT(dem.user_pseudo_id), 10)) <= 7\n",
    "        THEN 'TRAIN'\n",
    "      WHEN ABS(MOD(FARM_FINGERPRINT(dem.user_pseudo_id), 10)) = 8\n",
    "        THEN 'EVAL'\n",
    "      WHEN ABS(MOD(FARM_FINGERPRINT(dem.user_pseudo_id), 10)) = 9\n",
    "        THEN 'TEST'    \n",
    "          ELSE '' END AS data_split\n",
    "  FROM\n",
    "    bqmlga4.user_churn chu\n",
    "  LEFT OUTER JOIN\n",
    "    bqmlga4.user_demographics dem\n",
    "  ON \n",
    "    chu.user_pseudo_id = dem.user_pseudo_id\n",
    "  LEFT OUTER JOIN \n",
    "    bqmlga4.user_behavior beh\n",
    "  ON\n",
    "    chu.user_pseudo_id = beh.user_pseudo_id\n",
    "  WHERE chu.bounced = 0\n",
    "  );\n",
    "\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  bqmlga4.ml_features\n",
    "LIMIT 10"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ccc4f5f9",
   "metadata": {},
   "source": [
    "### Validate feature splits"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0af097e0",
   "metadata": {},
   "source": [
    "Run the query below to validate the number of examples in each data partition for the 80% train |10% eval |10% test split."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cb419c16",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT_ID\n",
    "\n",
    "SELECT\n",
    "  data_split,\n",
    "  COUNT(*) AS n_examples\n",
    "FROM bqmlga4.ml_features\n",
    "GROUP BY data_split"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c2767dae",
   "metadata": {},
   "source": [
    "## Train and tune a BQML XGBoost propensity model to predict customer churn"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a3a861a0",
   "metadata": {},
   "source": [
    "The following code trains and tunes the hyperparameters for an XGBoost model. TO provide a minimal demonstration of BQML hyperparameter tuning in this lab, this model will take about 18 min to train and tune with its restricted search space and low number of trials. In practice, you would generally want at [least 10 trials per hyperparameter](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-hyperparameter-tuning#how_many_trials_do_i_need_to_tune_a_model) to achieve improved results.\n",
    "\n",
    "For more information on the default hyperparameters used, you can read the documentation:\n",
    "[CREATE MODEL statement for Boosted Tree models using XGBoost](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-create-boosted-tree)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da974b2b",
   "metadata": {},
   "source": [
    "|Model   | BQML model_type | Advantages | Disadvantages| \n",
    "|:-------|:----------:|:----------:|-------------:|\n",
    "|XGBoost |     BOOSTED_TREE_CLASSIFIER [(documentation)](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-create-boosted-tree)       |   High model performance with feature importances and explainability | Slower to train than BQML LOGISTIC_REG |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "577c9658",
   "metadata": {},
   "source": [
    "Note: When you run the CREATE MODEL statement, BigQuery ML can automatically split your data into training and test so you can immediately evaluate your model's performance after training. This is a great option for fast model prototyping. In this lab, however, you split your data manually above using hashing for reproducible data splits that can be used comparing model evaluations across different runs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "96f84f45",
   "metadata": {},
   "outputs": [],
   "source": [
    "MODEL_NAME=\"churn_xgb\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d4bbf26",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT_ID\n",
    "\n",
    "CREATE OR REPLACE MODEL bqmlga4.churn_xgb\n",
    "\n",
    "OPTIONS(\n",
    "  MODEL_TYPE=\"BOOSTED_TREE_CLASSIFIER\",\n",
    "  # Declare label column.\n",
    "  INPUT_LABEL_COLS=[\"churned\"],\n",
    "  # Specify custom data splitting using the `data_split` column.\n",
    "  DATA_SPLIT_METHOD=\"CUSTOM\",\n",
    "  DATA_SPLIT_COL=\"data_split\",\n",
    "  # Enable Vertex Explainable AI aggregated feature attributions.\n",
    "  ENABLE_GLOBAL_EXPLAIN=True,\n",
    "  # Hyperparameter tuning arguments.\n",
    "  num_trials=8,\n",
    "  max_parallel_trials=4,\n",
    "  HPARAM_TUNING_OBJECTIVES=[\"roc_auc\"],\n",
    "  EARLY_STOP=True,\n",
    "  # Hyperpameter search space.\n",
    "  LEARN_RATE=HPARAM_RANGE(0.01, 0.1),\n",
    "  MAX_TREE_DEPTH=HPARAM_CANDIDATES([5,6])\n",
    ") AS\n",
    "\n",
    "SELECT\n",
    "  * EXCEPT(user_pseudo_id)\n",
    "FROM\n",
    "  bqmlga4.ml_features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f2beffd0",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT_ID\n",
    "\n",
    "SELECT *\n",
    "FROM\n",
    "  ML.TRIAL_INFO(MODEL `bqmlga4.churn_xgb`);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fbb9dc3a",
   "metadata": {},
   "source": [
    "## Evaluate BQML XGBoost model performance"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "19c50568",
   "metadata": {},
   "source": [
    "Once training is finished, you can run [ML.EVALUATE](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-evaluate) to return model evaluation metrics. By default, all model trials will be returned so the below query just returns the model performance for optimal first trial."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1bb9896b",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT_ID\n",
    "\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  ML.EVALUATE(MODEL bqmlga4.churn_xgb)\n",
    "WHERE trial_id=1;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e0537c9",
   "metadata": {},
   "source": [
    "ML.EVALUATE generates the [precision, recall](https://developers.google.com/machine-learning/crash-course/classification/precision-and-recall), [accuracy](https://developers.google.com/machine-learning/crash-course/classification/accuracy), [log_loss](https://en.wikipedia.org/wiki/Loss_functions_for_classification#Logistic_loss), [f1_score](https://en.wikipedia.org/wiki/F-score) and [roc_auc](https://developers.google.com/machine-learning/crash-course/classification/roc-and-auc) using the default classification threshold of 0.5, which can be modified by using the optional `THRESHOLD` parameter."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22da8688",
   "metadata": {},
   "source": [
    "Next, use the [ML.CONFUSION_MATRIX](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-confusion) function to return a confusion matrix for the input classification model and input data.\n",
    "\n",
    "For more information on confusion matrices, you can read through a detailed explanation [here](https://developers.google.com/machine-learning/crash-course/classification/true-false-positive-negative)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f15b2e45",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT_ID\n",
    "\n",
    "SELECT\n",
    "  expected_label,\n",
    "  _0 AS predicted_0,\n",
    "  _1 AS predicted_1\n",
    "FROM\n",
    "  ML.CONFUSION_MATRIX(MODEL bqmlga4.churn_xgb)\n",
    "WHERE trial_id=1;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "64cdbf52",
   "metadata": {},
   "source": [
    "You can also plot the AUC-ROC curve by using [ML.ROC_CURVE](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-roc) to return the metrics for different threshold values for the model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f1f9e493",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery df_roc --project $PROJECT_ID\n",
    "\n",
    "SELECT * FROM ML.ROC_CURVE(MODEL bqmlga4.churn_xgb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "da715945",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_roc.plot(x=\"false_positive_rate\", y=\"recall\", title=\"AUC-ROC curve\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e51a9bc",
   "metadata": {},
   "source": [
    "## Inspect global feature attributions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c1e4a89b",
   "metadata": {},
   "source": [
    "To provide further context to your model performance, you can use the [ML.GLOBAL_EXPLAIN](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-global-explain#get_global_feature_importance_for_each_class_of_a_boosted_tree_classifier_model) function which leverages Vertex Explainable AI as a back-end. [Vertex Explainable AI](https://cloud.google.com/vertex-ai/docs/explainable-ai) helps you understand your model's outputs for classification and regression tasks. Specifically, Vertex AI tells you how much each feature in the data contributed to your model's predicted result. You can then use this information to verify that the model is behaving as expected, identify and mitigate biases in your models, and get ideas for ways to improve your model and your training data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b505dae3",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT_ID\n",
    "\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  ML.GLOBAL_EXPLAIN(MODEL bqmlga4.churn_xgb)\n",
    "ORDER BY\n",
    "  attribution DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b471e6ee",
   "metadata": {},
   "source": [
    "## Generate batch predictions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea8623c1",
   "metadata": {},
   "source": [
    "You can generate batch predictions for your BQML XGBoost model using [ML.PREDICT](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-predict)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b117c407",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT_ID\n",
    "\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  ML.PREDICT(MODEL bqmlga4.churn_xgb,\n",
    "  (SELECT * FROM bqmlga4.ml_features WHERE data_split = \"TEST\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32dac74c",
   "metadata": {},
   "source": [
    "The following query returns the probability that the user will return after 24 hrs. The higher the probability and closer it is to 1, the more likely the user is predicted to churn, and the closer it is to 0, the more likely the user is predicted to return."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7411bff",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT_ID\n",
    "\n",
    "CREATE OR REPLACE TABLE bqmlga4.churn_predictions AS (\n",
    "SELECT\n",
    "  user_pseudo_id,\n",
    "  churned,\n",
    "  predicted_churned,\n",
    "  predicted_churned_probs[OFFSET(0)].prob as probability_churned\n",
    "FROM\n",
    "  ML.PREDICT(MODEL bqmlga4.churn_xgb,\n",
    "  (SELECT * FROM bqmlga4.ml_features))\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c037410",
   "metadata": {},
   "source": [
    "## Export a BQML model to Vertex AI for online predictions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a36d9005",
   "metadata": {},
   "source": [
    "See the official BigQuery ML Guide: [Exporting a BigQuery ML model for online prediction](https://cloud.google.com/bigquery-ml/docs/export-model-tutorial) for additional details."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6593da8c",
   "metadata": {},
   "source": [
    "### Export BQML model to GCS"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4dfedfdd",
   "metadata": {},
   "source": [
    "You will use the `bq extract` command in the `bq` command-line tool to export your BQML XGBoost model assets to Google Cloud Storage for persistence. See the [documentation](https://cloud.google.com/bigquery-ml/docs/exporting-models) for additional model export options."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "94f50a47",
   "metadata": {},
   "outputs": [],
   "source": [
    "BQ_MODEL = f\"{BQ_DATASET}.{MODEL_NAME}\"\n",
    "BQ_MODEL_EXPORT_DIR = f\"gs://{GCS_BUCKET}/{MODEL_NAME}\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ee61430a",
   "metadata": {},
   "outputs": [],
   "source": [
    "!bq --location=$BQ_LOCATION extract \\\n",
    "--destination_format ML_XGBOOST_BOOSTER \\\n",
    "--model $BQ_MODEL \\\n",
    "$BQ_MODEL_EXPORT_DIR"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f336458",
   "metadata": {},
   "source": [
    "Navigate to [Google Cloud Storage](https://pantheon.corp.google.com/storage) in Google Cloud Console to `\"gs://{GCS_BUCKET}/{MODEL_NAME}\"`. Validate that you see your exported model assets in the below format:\n",
    "\n",
    "```\n",
    "|--/{GCS_BUCKET}/{MODEL_NAME}/\n",
    "   |--/assets/                       # Contains preprocessing code.  \n",
    "      |--0_categorical_label.txt     # Contains country vocabulary.\n",
    "      |--1_categorical_label.txt     # Contains operating_system vocabulary.\n",
    "      |--2_categorical_label.txt     # Contains language vocabulary.\n",
    "      |--model_metadata.json         # contains model feature and label mappings.\n",
    "   |--main.py                        # Can be called for local training runs.\n",
    "   |--model.bst                      # XGBoost saved model format.\n",
    "   |--xgboost_predictor-0.1.tar.gz   # Compress XGBoost model with prediction function. \n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71122b86",
   "metadata": {},
   "source": [
    "### Upload BQML model to Vertex AI from GCS"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc20e5e7",
   "metadata": {},
   "source": [
    "Vertex AI contains optimized pre-built training and prediction containers for popular ML frameworks such as TensorFlow, Pytorch, as well as XGBoost. You will upload your XGBoost from GCS to Vertex AI and provide the [latest pre-built Vertex XGBoost prediction container](https://cloud.google.com/vertex-ai/docs/predictions/pre-built-containers) to execute your model code to generate predictions in the cells below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c86cd67e",
   "metadata": {},
   "outputs": [],
   "source": [
    "IMAGE_URI='us-docker.pkg.dev/vertex-ai/prediction/xgboost-cpu.1-4:latest'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e3278567",
   "metadata": {},
   "outputs": [],
   "source": [
    "model = vertexai.Model.upload(\n",
    "    display_name=MODEL_NAME,\n",
    "    artifact_uri=BQ_MODEL_EXPORT_DIR,\n",
    "    serving_container_image_uri=IMAGE_URI,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0874197c",
   "metadata": {},
   "source": [
    "### Deploy a Vertex `Endpoint` for online predictions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1410ebc6",
   "metadata": {},
   "source": [
    "Before you use your model to make predictions, you need to deploy it to an `Endpoint` object. When you deploy a model to an `Endpoint`, you associate physical (machine) resources with that model to enable it to serve online predictions. Online predictions have low latency requirements; providing resources to the model in advance reduces latency. You can do this by calling the deploy function on the `Model` resource. This will do two things:\n",
    "\n",
    "1. Create an `Endpoint` resource for deploying the `Model` resource to.\n",
    "2. Deploy the `Model` resource to the `Endpoint` resource.\n",
    "\n",
    "The `deploy()` function takes the following parameters:\n",
    "\n",
    "* `deployed_model_display_name`: A human readable name for the deployed model.\n",
    "* `traffic_split`: Percent of traffic at the endpoint that goes to this model, which is specified as a dictionary of one or more key/value pairs. If only one model, then specify as { \"0\": 100 }, where \"0\" refers to this model being uploaded and 100 means 100% of the traffic.\n",
    "* `machine_type`: The type of machine to use for training.\n",
    "* `accelerator_type`: The hardware accelerator type.\n",
    "* `accelerator_count`: The number of accelerators to attach to a worker replica.\n",
    "* `starting_replica_count`: The number of compute instances to initially provision.\n",
    "* `max_replica_count`: The maximum number of compute instances to scale to. In this lab, only one instance is provisioned.\n",
    "* `explanation_parameters`: Metadata to configure the Explainable AI learning method.\n",
    "* `explanation_metadata`: Metadata that describes your TensorFlow model for Explainable AI such as features, input and output tensors.\n",
    "\n",
    "Note: this can take about 3-5 minutes to provision prediction resources for your model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df5368fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "endpoint = model.deploy(\n",
    "    traffic_split={\"0\": 100},\n",
    "    machine_type=\"e2-standard-2\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc8448a1",
   "metadata": {},
   "source": [
    "### Query model for online predictions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b67ca13",
   "metadata": {},
   "source": [
    "XGBoost only takes numerical feature inputs. When you trained your BQML model above with CREATE MODEL statement, it automatically handled encoding of categorical features such as user `country`, `operating system`, and `language` into numeric representations. In order for our exported model to generate online predictions, you will use the categorical feature vocabulary files exported under the `assets/` folder of your model directory and the Scikit-Learn preprocessing code below to map your test instances to numeric values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6b647538",
   "metadata": {},
   "outputs": [],
   "source": [
    "CATEGORICAL_FEATURES = ['country',\n",
    "                        'operating_system',\n",
    "                        'language']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18bb0e82",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.preprocessing import OrdinalEncoder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "350c5727",
   "metadata": {},
   "outputs": [],
   "source": [
    "def _build_cat_feature_encoders(cat_feature_list, gcs_bucket, model_name, na_value='Unknown'):\n",
    "    \"\"\"Build categorical feature encoders for mapping text to integers for XGBoost inference. \n",
    "    Args:\n",
    "      cat_feature_list (list): List of string feature names.\n",
    "      gcs_bucket (str): A string path to your Google Cloud Storage bucket.\n",
    "      model_name (str): A string model directory in GCS where your BQML model was exported to.\n",
    "      na_value (str): default is 'Unknown'. String value to replace any vocab NaN values prior to encoding.\n",
    "    Returns:\n",
    "      feature_encoders (dict): A dictionary containing OrdinalEncoder objects for integerizing \n",
    "        categorical features that has the format [feature] = feature encoder.\n",
    "    \"\"\"\n",
    "    \n",
    "    feature_encoders = {}\n",
    "    \n",
    "    for idx, feature in enumerate(cat_feature_list):\n",
    "        feature_encoder = OrdinalEncoder(handle_unknown=\"use_encoded_value\", unknown_value=-1)\n",
    "        feature_vocab_file = f\"gs://{gcs_bucket}/{model_name}/assets/{idx}_categorical_label.txt\"\n",
    "        feature_vocab_df = pd.read_csv(feature_vocab_file, delimiter = \"\\t\", header=None).fillna(na_value)\n",
    "        feature_encoder.fit(feature_vocab_df.values)\n",
    "        feature_encoders[feature] = feature_encoder\n",
    "    \n",
    "    return feature_encoders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "809875a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "def preprocess_xgboost(instances, cat_feature_list, feature_encoders):\n",
    "    \"\"\"Transform instances to numerical values for inference.\n",
    "    Args:\n",
    "      instances (list[dict]): A list of feature dictionaries with the format feature: value. \n",
    "      cat_feature_list (list): A list of string feature names.\n",
    "      feature_encoders (dict): A dictionary with the format feature: feature_encoder.\n",
    "    Returns:\n",
    "      transformed_instances (list[list]): A list of lists containing numerical feature values needed\n",
    "        for Vertex XGBoost inference.\n",
    "    \"\"\"\n",
    "    transformed_instances = []\n",
    "    \n",
    "    for instance in instances:\n",
    "        for feature in cat_feature_list:\n",
    "            feature_int = feature_encoders[feature].transform([[instance[feature]]]).item()\n",
    "            instance[feature] = feature_int\n",
    "            instance_list = list(instance.values())\n",
    "        transformed_instances.append(instance_list)\n",
    "\n",
    "    return transformed_instances"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eae41101",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Build a dictionary of ordinal categorical feature encoders.\n",
    "feature_encoders = _build_cat_feature_encoders(CATEGORICAL_FEATURES, GCS_BUCKET, MODEL_NAME)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "201f0aa9",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery test_df --project $PROJECT_ID \n",
    "\n",
    "SELECT* EXCEPT (user_pseudo_id, churned, data_split)\n",
    "FROM bqmlga4.ml_features\n",
    "WHERE data_split=\"TEST\"\n",
    "LIMIT 3;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d397bc03",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert dataframe records to feature dictionaries for preprocessing by feature name.\n",
    "test_instances = test_df.astype(str).to_dict(orient='records')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e19ade61",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Apply preprocessing to transform categorical features and return numerical instances for prediction.\n",
    "transformed_test_instances = preprocess_xgboost(test_instances, CATEGORICAL_FEATURES, feature_encoders)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "873c0db2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Generate predictions from model deployed to Vertex AI Endpoint.\n",
    "predictions = endpoint.predict(instances=transformed_test_instances)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a3a08430",
   "metadata": {},
   "outputs": [],
   "source": [
    "for idx, prediction in enumerate(predictions.predictions):\n",
    "    # Class labels [1,0] retrieved from model_metadata.json in GCS model dir.\n",
    "    # BQML binary classification default is 0.5 with above \"Churn\" and below \"Not Churn\".\n",
    "    is_churned = \"Churn\" if prediction[0] >= 0.5 else \"Not Churn\"\n",
    "    print(f\"Prediction: Customer {idx} - {is_churned} {prediction}\")\n",
    "    print(test_df.iloc[idx].astype(str).to_json() + \"\\n\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d7a4e13f",
   "metadata": {},
   "source": [
    "## Next steps"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f0d1f2c",
   "metadata": {},
   "source": [
    "Congratulations! In this lab, you trained, tuned, explained, and deployed a BigQuery ML user churn model to generate high business impact batch and online churn predictions to target customers likely to churn with interventions such as in-game rewards and reminder notifications.\n",
    "\n",
    "In this lab, you used `user_psuedo_id` as a user identifier. As next steps, you can extend this code further by having your application return a `user_id` to Google Analytics so you can join your model's predictions with additional first-party data such as purchase history and marketing engagement data. This enables you to integrate batch predictions into Looker dashboards to help product teams prioritize user experience improvements and marketing teams create targeted user interventions such as reminder emails to improve retention. \n",
    "\n",
    "Through having your model in Vertex AI Prediction, you also have a scalable prediction service to call from your application to directly integrate online predictions in order to to tailor personalized user game experiences and allow for targeted habit-building notifications.\n",
    "\n",
    "As you collect more data from your users, you may want to regularly evaluate your model on fresh data and re-train the model if you notice that the model quality is decaying. [Vertex Pipelines](https://cloud.google.com/vertex-ai/docs/pipelines/introduction) can help you to automate, monitor, and govern your ML solutions by orchestrating your BQML workflow in a serverless manner, and storing your workflow's artifacts using [Vertex ML Metadata](https://cloud.google.com/vertex-ai/docs/ml-metadata/introduction). For another alternative for continuous BQML models, checkout the blog post [Continuous model evaluation with BigQuery ML, Stored Procedures, and Cloud Scheduler](https://cloud.google.com/blog/topics/developers-practitioners/continuous-model-evaluation-bigquery-ml-stored-procedures-and-cloud-scheduler).\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a04b58a0",
   "metadata": {},
   "source": [
    "## License"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1593ed77",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Copyright 2021 Google LLC\n",
    "#\n",
    "# Licensed under the Apache License, Version 2.0 (the \"License\");\n",
    "# you may not use this file except in compliance with the License.\n",
    "# You may obtain a copy of the License at\n",
    "#\n",
    "#     https://www.apache.org/licenses/LICENSE-2.0\n",
    "#\n",
    "# Unless required by applicable law or agreed to in writing, software\n",
    "# distributed under the License is distributed on an \"AS IS\" BASIS,\n",
    "# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n",
    "# See the License for the specific language governing permissions and\n",
    "# limitations under the License."
   ]
  }
 ],
 "metadata": {
  "environment": {
   "name": "tf2-gpu.2-3.m80",
   "type": "gcloud",
   "uri": "gcr.io/deeplearning-platform-release/tf2-gpu.2-3:m80"
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
